import os
import json
import tempfile
from itertools import count

from openpyxl import load_workbook

from common.db_util import MySqlDb
from django.http import HttpResponse
from django.shortcuts import render
from rest_framework.views import APIView

from invoice.models import DifyBindUser
from system.system_models import User


# Create your views here.

# 简单演示视图 hello
def hello(request):
    return HttpResponse("Hello Django!")  # 返回文本响应

# 演示微服务restful api
from rest_framework.response import Response
class ArticleDetail(APIView):
    def get(self, request):
        articles = [{"id": 1, "title": "Django for APIs"},
                    {"id": 2, "title": "Django Rest Framework"},
                    {"id": 3, "title": "Django Rest Framework 3"},
                    {"id": 4, "title": "Django Rest Framework 4"},
                    {"id": 5, "title": "Django Rest Framework 5"}
                    ]
        return Response(articles, status=200)


#演示iframe嵌入
def embedding_app(request):
    return render(request, 'page/dify/EmbeddingApp.html')

#4.4 Dify调用Http集成—查询数据,返回json格式,接收参数id
def query_data(request):
    id = request.GET.get('userId')
    print("请求参数userId:", id)
    return HttpResponse("{'name':'张三','age':18}")

#4.5 Dify调用Http集成—传递发票文件,接收文件流,保存到本地，图片格式
def save_invoice_file(request):
    #取出用户id参数
    userId = request.GET.get('userId')
    print("请求参数userId:", userId)
    #项目路径
    project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    print("项目路径:", project_path)

    if request.method == 'POST':
        file = request.FILES['file']
        with open(f'{project_path}/static/invoice_files/{file.name}', 'wb+') as destination:
            for chunk in file.chunks():
                destination.write(chunk)

        #json格式返回
        return HttpResponse(f"{{'status':'success','message':'文件{file.name}上传成功'}}")
    return HttpResponse("请求方法错误")

# 读取invoice_output.xlsx件及输出文件流 API

class InvoiceOutputView(APIView):
    def get(self, request):
        #取出用户id参数
        userId = request.GET.get('userId')
        print("请求参数userId:", userId)
        try:
            import openpyxl
            # 使用二进制模式打开文件并直接返回其内容
            with open('invoice_output.xlsx', 'rb') as excel_file:
                excel_data = excel_file.read()

            output = HttpResponse(excel_data,
                                  content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
            output['Content-Disposition'] = 'attachment; filename="invoice_output.xlsx"'
            return output

        except FileNotFoundError:
            return Response({"status": "error", "message": "文件未找到"}, status=404)
        except Exception as e:
            return Response({"status": "error", "message": str(e)}, status=500)

#dify用户绑定
class DifyBindUserView(APIView):
    def get(self, request):
        #取出用户id参数,dify用户id参数
        difyUserId = request.GET.get('dify_user_id')
        print("请求参数userId:", difyUserId)
        #app用户ID,从session取
        user:User = request.session.get('user_info')
        print("session用户ID:", user.get('id'))
        appUserId = user.get('id')
        #是否已绑定，查询数据库
        bind_user = MySqlDb.get_mysql_session().query(DifyBindUser).filter_by(app_user_id=appUserId).first()
        if bind_user:
            return Response({f"status": "error", "message": f"用户已绑定，用户ID：{bind_user.dify_user_id}"}, status=400)

        #绑定用户
        bind_user = DifyBindUser(app_user_id=appUserId, dify_user_id=difyUserId)
        MySqlDb.get_mysql_session().add(bind_user)
        MySqlDb.get_mysql_session().commit()
        return Response({"status": "success", "message": "用户绑定成功"}, status=200)

class ExportExcelToDifyView(APIView):
    def post(self, request):
        """
        向dify导出excel文件
        功能：将发票数据转换为Excel文件并以文件流形式输出到浏览器
        """
        temp_file_path = None
        try:
            # 1. 从请求表单中取出发票数据
            invoice_data_str = request.POST.get('invoice_data')
            if not invoice_data_str:
                return Response({"status": "error", "message": "发票数据不能为空"}, status=400)
            
            print("请求参数invoice_data:", invoice_data_str)
            # 去掉特殊字符
            invoice_data_str = invoice_data_str.replace("```json", "").replace("```", "")

            # 2. 解析JSON数据
            invoice_data_list = json.loads(invoice_data_str)
            if not invoice_data_list or not isinstance(invoice_data_list, list):
                return Response({"status": "error", "message": "发票数据格式错误"}, status=400)
            
            # 3. 只取首条发票数据
            invoice_data = invoice_data_list[0]
            
            # 4. 准备模板路径和临时输出路径
            project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
            template_path = os.path.join(project_path, 'excel_templ', 'invoice_export_template(singleton).xlsx')
            temp_file_path = os.path.join(project_path, 'invoice_output.xlsx')
            
            # 5. 转换数据并生成Excel
            self._convert_and_export_invoice(invoice_data, template_path, temp_file_path)
            
            # 6. 读取生成的Excel文件
            with open(temp_file_path, 'rb') as excel_file:
                excel_data = excel_file.read()
            
            # 7. 创建HTTP响应，设置正确的Content-Type
            response = HttpResponse(
                excel_data,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = 'attachment; filename="invoice_output.xlsx"'
            
            return response
            
        except json.JSONDecodeError as e:
            return Response({"status": "error", "message": f"JSON解析错误: {str(e)}"}, status=400)
        except FileNotFoundError as e:
            return Response({"status": "error", "message": f"模板文件未找到: {str(e)}"}, status=404)
        except Exception as e:
            return Response({"status": "error", "message": f"导出失败: {str(e)}"}, status=500)
        finally:
            # 8. 删除临时文件
            if temp_file_path and os.path.exists(temp_file_path):
                try:
                    os.remove(temp_file_path)
                    print(f"临时文件已删除: {temp_file_path}")
                except Exception as e:
                    print(f"删除临时文件失败: {str(e)}")
    
    def _convert_and_export_invoice(self, invoice_data, template_path, output_path):
        """
        将发票数据转换并导出到Excel模板
        
        :param invoice_data: 发票数据字典
        :param template_path: 模板文件路径
        :param output_path: 输出文件路径
        """
        # 加载模板
        wb = load_workbook(template_path)
        ws = wb.active
        
        # 填充发票基本信息（合并单元格填充起始单元格）
        # B2:H2 - 发票代码，实际是发票号码
        ws['B2'] = invoice_data.get('发票号码', '')
        
        # B3:H3 - 发票号码（这里填发票类型）
        ws['B3'] = invoice_data.get('发票类型', '')
        
        # B4:H4 - 开票日期
        ws['B4'] = invoice_data.get('开票日期', '')
        
        # 填充购买方信息
        buyer_info = invoice_data.get('购买方信息', {})
        # C5:D5 - 购买方名称
        ws['C5'] = buyer_info.get('名称', '')
        # C6:D6 - 购买方识别号
        ws['C6'] = buyer_info.get('统一社会信用代码/纳税人识别号', '')
        # C7:D7 - 购买方地址电话（可选）
        ws['C7'] = buyer_info.get('地址电话', '')
        # C8:D8 - 购买方开户行及账号（可选）
        ws['C8'] = buyer_info.get('开户行及账号', '')
        
        # F5:H8 - 密码区（可选，电子发票通常没有）
        # ws['F5'] = invoice_data.get('密码区', '')
        
        # 填充项目明细（从第10行开始）
        items = invoice_data.get('项目明细', [])
        start_row = 10
        for idx, item in enumerate(items):
            row = start_row + idx
            ws[f'A{row}'] = item.get('项目名称', '')  # 项目名称
            ws[f'B{row}'] = item.get('规格型号', '')  # 规格型号
            ws[f'C{row}'] = item.get('单位', '')  # 单位
            ws[f'D{row}'] = item.get('数量', '')  # 数量
            ws[f'E{row}'] = item.get('单价', '')  # 单价
            ws[f'F{row}'] = item.get('金额', '')  # 金额
            ws[f'G{row}'] = item.get('税率/征收率', '')  # 税率
            ws[f'H{row}'] = item.get('税额', '')  # 税额
        
        # 填充合计信息
        total_info = invoice_data.get('合计', {})
        # F13 - 合计金额（A13:D13是"合计："标签）
        ws['F13'] = total_info.get('金额', '')
        # H13 - 合计税额
        ws['H13'] = total_info.get('税额', '')
        
        # F14 - 价税合计（A14:D14是"合税金额："标签）
        ws['F14'] = invoice_data.get('价税合计(小写)', '')
        # H14 - 价税合计大写
        ws['H14'] = invoice_data.get('价税合计(大写)', '')
        
        # 填充销售方信息
        seller_info = invoice_data.get('销售方信息', {})
        # C15:D15 - 销售方名称
        ws['C15'] = seller_info.get('名称', '')
        # C16:D16 - 销售方识别号
        ws['C16'] = seller_info.get('统一社会信用代码/纳税人识别号', '')
        # C17:D17 - 销售方地址电话（可选）
        ws['C17'] = seller_info.get('地址电话', '')
        # C18:D18 - 销售方开户行及账号（可选）
        ws['C18'] = seller_info.get('开户行及账号', '')
        
        # E15:E18 - 备注区域
        ws['E15'] = invoice_data.get('备注', '')
        
        # F15:H18 - 开票人等信息（可选）
        ws['F15'] = f"开票人: {invoice_data.get('开票人', '')}"
        
        # 保存文件
        wb.save(output_path)
        print(f"Excel文件已生成: {output_path}")


class ExportExcelToDifyView2(APIView):
    """
    向dify导出excel文件 - 表格式模板（多张发票）
    功能：将发票数据转换为Excel文件并以文件流形式输出到浏览器
    模板：invoice_export_template(many).xlsx - 表格式，忽略项目明细
    """
    
    def post(self, request):
        """
        处理发票数据导出请求
        """
        temp_file_path = None
        try:
            # 1. 从请求表单中取出发票数据
            invoice_data_str = request.POST.get('invoice_data')
            if not invoice_data_str:
                return Response({"status": "error", "message": "发票数据不能为空"}, status=400)
            
            print("请求参数invoice_data:", invoice_data_str)
            # 去掉特殊字符
            invoice_data_str = invoice_data_str.replace("```json", "").replace("```", "")
            
            # 2. 解析JSON数据
            invoice_data_list = json.loads(invoice_data_str)
            if not invoice_data_list or not isinstance(invoice_data_list, list):
                return Response({"status": "error", "message": "发票数据格式错误"}, status=400)
            
            # 3. 准备模板路径和临时输出路径
            project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
            template_path = os.path.join(project_path, 'excel_templ', 'invoice_export_template(many).xlsx')
            temp_file_path = os.path.join(project_path, 'invoice_output.xlsx')
            
            # 4. 转换数据并生成Excel（严格按照表格模板，忽略项目明细）
            self._convert_and_export_invoices(invoice_data_list, template_path, temp_file_path)
            
            # 5. 读取生成的Excel文件
            with open(temp_file_path, 'rb') as excel_file:
                excel_data = excel_file.read()
            
            # 6. 创建HTTP响应，设置正确的Content-Type
            response = HttpResponse(
                excel_data,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = 'attachment; filename="invoice_output.xlsx"'
            
            return response
            
        except json.JSONDecodeError as e:
            return Response({"status": "error", "message": f"JSON解析错误: {str(e)}"}, status=400)
        except FileNotFoundError as e:
            return Response({"status": "error", "message": f"模板文件未找到: {str(e)}"}, status=404)
        except Exception as e:
            return Response({"status": "error", "message": f"导出失败: {str(e)}"}, status=500)
        finally:
            # 7. 删除临时文件
            if temp_file_path and os.path.exists(temp_file_path):
                try:
                    os.remove(temp_file_path)
                    print(f"临时文件已删除: {temp_file_path}")
                except Exception as e:
                    print(f"删除临时文件失败: {str(e)}")
    
    def _convert_and_export_invoices(self, invoice_data_list, template_path, output_path):
        """
        将发票数据转换并导出到Excel模板（表格式）
        注意：忽略项目明细，只导出发票基本信息
        
        :param invoice_data_list: 发票数据列表
        :param template_path: 模板文件路径
        :param output_path: 输出文件路径
        """
        # 加载模板
        wb = load_workbook(template_path)
        ws = wb.active
        
        # 表格列顺序（第1行是表头）：
        # A:发票类型, B:发票代码, C:发票号码, D:价税合计(大写), E:价税合计(小写)
        # F:开票日期, G:开票人, H:销售方名称, I:销售方纳税人识别号
        # J:销售方地址电话, K:销售方银行账号, L:购买方名称, M:购买方纳税人识别号
        # N:购买方银行账号, O:备注
        
        # 从第2行开始填充数据
        start_row = 2
        
        for idx, invoice_data in enumerate(invoice_data_list):
            row = start_row + idx
            
            # 获取嵌套数据
            buyer_info = invoice_data.get('购买方信息', {})
            seller_info = invoice_data.get('销售方信息', {})
            
            # 填充表格数据（严格按照模板列顺序，忽略项目明细）
            ws[f'A{row}'] = invoice_data.get('发票类型', '')  # 发票类型
            ws[f'B{row}'] = invoice_data.get('发票代码', '')  # 发票代码（可能为空）
            ws[f'C{row}'] = invoice_data.get('发票号码', '')  # 发票号码
            ws[f'D{row}'] = invoice_data.get('价税合计(大写)', '')  # 价税合计(大写)
            ws[f'E{row}'] = invoice_data.get('价税合计(小写)', '')  # 价税合计(小写)
            ws[f'F{row}'] = invoice_data.get('开票日期', '')  # 开票日期
            ws[f'G{row}'] = invoice_data.get('开票人', '')  # 开票人
            ws[f'H{row}'] = seller_info.get('名称', '')  # 销售方名称
            ws[f'I{row}'] = seller_info.get('统一社会信用代码/纳税人识别号', '')  # 销售方纳税人识别号
            ws[f'J{row}'] = seller_info.get('地址电话', '')  # 销售方地址电话（可选）
            ws[f'K{row}'] = seller_info.get('开户行及账号', '')  # 销售方银行账号（可选）
            ws[f'L{row}'] = buyer_info.get('名称', '')  # 购买方名称
            ws[f'M{row}'] = buyer_info.get('统一社会信用代码/纳税人识别号', '')  # 购买方纳税人识别号
            ws[f'N{row}'] = buyer_info.get('开户行及账号', '')  # 购买方银行账号（可选）
            ws[f'O{row}'] = invoice_data.get('备注', '')  # 备注
        
        # 保存文件
        wb.save(output_path)
        print(f"Excel文件已生成: {output_path}，共导出 {len(invoice_data_list)} 条发票数据")



#Dify调用Http集成—传递发票文件,存储发票图片（多张）
def save_invoice_images(request):
    #项目路径
    project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
    print("项目路径:", project_path)

    if request.method == 'POST':
        #多张发票图片处，files是一个列表
        files = request.FILES.getlist('files')
        # 1. 从请求表单中取出发票数据
        invoice_data_str = request.POST.get('invoice_data')
        if not invoice_data_str:
            return Response({"status": "error", "message": "发票数据不能为空"}, status=400)

        print("请求参数invoice_data:", invoice_data_str)
        # 去掉特殊字符
        invoice_data_str = invoice_data_str.replace("```json", "").replace("```", "")

        # 2. 解析JSON数据
        invoice_data_list = json.loads(invoice_data_str)
        if not invoice_data_list or not isinstance(invoice_data_list, list):
            return Response({"status": "error", "message": "发票数据格式错误"}, status=400)
        count:int = 0
        # 遍历列表，保存每个文件
        for file in files:
            file_name = file.name
            filePath = f"/static/invoice_files/{file_name}"
            with open(f'{project_path}{filePath}', 'wb+') as destination:
                for chunk in file.chunks():
                    destination.write(chunk)

            #取出发票
            invoice = invoice_data_list[count]
            #取出发票编号
            invoice_code = invoice.get('发票号码', '')
            #更新到数据库invoice表
            invoice_code = invoice.get('发票号码', '')
            sql = "update invoice set file_path = :file_path where invoice_code = :invoice_code"
            params = {"invoice_code": invoice_code, "file_path": filePath}
            MySqlDb.update(sql, params)
            count += 1
        #json格式返回
        return HttpResponse("{'status':'success','message':'文件转存成功'}")

# 查询dify用户是否已经绑定
def check_dify_user_bound(request):
    # 从登录会话中取出user_id
    user = request.session.get('user_info')
    app_user_id = user.get('id') if user else None

    if not app_user_id:
        return HttpResponse("{'status':'error','message':'用户未登录'}")

    # 查询数据库是否已经绑定dify用户，表名为dify_bind_user
    sql = "select * from dify_bind_user where app_user_id = :app_user_id"
    params = {"app_user_id": app_user_id}
    result = MySqlDb.query(sql, params)
    dify_user_id = None
    if result:
        # 已绑定，返回dify_user_id
        dify_user_id = result[0].get('dify_user_id')
   

    result = {"app_user_id": app_user_id, "dify_user_id": dify_user_id}
    return HttpResponse(json.dumps(result, ensure_ascii=False))

# 绑定dify用户
def bind_dify_user(request):
    # 从登录会话中取出user_id
    user = request.session.get('user_info')
    app_user_id = user.get('id') if user else None
    # 从请求参数中取出发票数据
    dify_user_id = request.POST.get('dify_user_id')
    if not dify_user_id:
        return HttpResponse("{'status':'error','message':'dify用户id不能为空'}")

    #删除表中已绑定的dify用户
    sql = "delete from dify_bind_user where app_user_id = :app_user_id"
    params = {"app_user_id": app_user_id}
    MySqlDb.update(sql, params)
    # 插入新的绑定记录
    sql = "insert into dify_bind_user (app_user_id, dify_user_id) values (:app_user_id, :dify_user_id)"
    params = {"app_user_id": app_user_id, "dify_user_id": dify_user_id}
    MySqlDb.insert(sql, params)
    #json格式返回
    return HttpResponse("{'code':200,'status':'success','message':'dify用户绑定成功'}")

#查询发票数据接口，使用class视图
class InvoiceList(APIView):
    def get(self, request):
        """
        查询发票列表（最新的100条数据）
        :param request: 请求对象
        :return: 发票列表数据
        """
        try:
            # 查询最新的100条发票数据，按创建时间倒序排列
            sql = """
                SELECT 
                    id,
                    invoice_type,
                    invoice_code,
                    issue_date,
                    buyer_name,
                    seller_name,
                    total_amount,
                    total_price_tax,
                    created_at
                FROM invoice
                ORDER BY created_at DESC
                LIMIT 100
            """
            
            # 使用MySqlDb.query方法查询数据
            invoice_list = MySqlDb.query(sql)
            
            # 返回成功响应
            return Response({
                "code": 200,
                "status": "success",
                "message": "查询成功",
                "data": invoice_list
            }, status=200)
            
        except Exception as e:
            # 返回错误响应
            print(f"查询发票列表失败: {str(e)}")
            return Response({
                "code": 500,
                "status": "error",
                "message": f"查询失败: {str(e)}"
            }, status=500)


# 页面访问路由：进入发票列表页面
def to_invoice_list(request):
    """
    渲染发票列表页面
    :param request: 请求对象
    :return: 渲染后的HTML页面
    """
    return render(request, 'page/invoice/invoice_list.html')


# 查询发票列表接口（带条件查询）
class QueryInvoiceList(APIView):
    def post(self, request):
        """
        根据查询条件查询发票列表
        支持的查询条件：
        - invoice_code: 发票号码
        - invoice_type: 发票类型
        - user_name: 用户姓名（通过关联表查询）
        - start_date: 开始日期
        - end_date: 结束日期
        
        :param request: 请求对象
        :return: 发票列表数据
        """
        try:
            # 获取查询参数
            invoice_code = request.data.get('invoice_code', '').strip()
            invoice_type = request.data.get('invoice_type', '').strip()
            user_name = request.data.get('user_name', '').strip()
            start_date = request.data.get('start_date', '').strip()
            end_date = request.data.get('end_date', '').strip()
            
            print(f"查询参数 - 发票号码: {invoice_code}, 发票类型: {invoice_type}, 用户姓名: {user_name}, 开始日期: {start_date}, 结束日期: {end_date}")
            
            # 构建SQL查询
            # 关联关系：invoice.dify_user_id = dify_bind_user.dify_user_id
            # dify_bind_user.app_user_id = sys_user.id
            # 注意：使用COLLATE utf8mb4_unicode_ci统一排序规则，避免字符集冲突
            sql = """
                SELECT DISTINCT
                    i.id,
                    i.invoice_type,
                    i.invoice_code,
                    i.issue_date,
                    i.buyer_name,
                    i.seller_name,
                    i.total_amount,
                    i.total_price_tax,
                    i.created_at,
                    u.real_name as user_name
                FROM invoice i
                LEFT JOIN dify_bind_user dbu ON i.dify_user_id COLLATE utf8mb4_unicode_ci = dbu.dify_user_id COLLATE utf8mb4_unicode_ci
                LEFT JOIN sys_user u ON dbu.app_user_id = u.id
                WHERE 1=1
            """
            
            # 动态添加查询条件
            params = {}
            
            # 发票号码查询（模糊匹配）
            if invoice_code:
                sql += " AND i.invoice_code LIKE :invoice_code"
                params['invoice_code'] = f"%{invoice_code}%"
            
            # 发票类型查询（精确匹配）
            if invoice_type:
                sql += " AND i.invoice_type = :invoice_type"
                params['invoice_type'] = invoice_type
            
            # 用户姓名查询（模糊匹配）
            if user_name:
                sql += " AND u.real_name LIKE :user_name"
                params['user_name'] = f"%{user_name}%"
            
            # 日期范围查询
            if start_date:
                sql += " AND i.created_at >= :start_date"
                params['start_date'] = start_date
            
            if end_date:
                sql += " AND i.created_at <= :end_date"
                params['end_date'] = end_date
            
            # 排序：按创建时间倒序，限制100条
            sql += " ORDER BY i.created_at DESC LIMIT 100"
            
            # 执行查询
            print(f"执行SQL: {sql}")
            print(f"查询参数: {params}")
            
            invoice_list = MySqlDb.query(sql, params)
            
            print(f"查询结果数量: {len(invoice_list)}")
            
            # 返回成功响应
            return Response({
                "code": 200,
                "status": "success",
                "message": "查询成功",
                "data": invoice_list
            }, status=200)
            
        except Exception as e:
            # 返回错误响应
            print(f"查询发票列表失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return Response({
                "code": 500,
                "status": "error",
                "message": f"查询失败: {str(e)}"
            }, status=500)

#查询发票详情接口
class QueryInvoiceDetail(APIView):
    def get(self, request):
        """
        根据发票ID查询发票详情信息
        :param request: 请求对象，包含参数id（发票ID）
        :return: 发票详情数据，包括基本信息和项目明细
        """
        try:
            # 获取发票ID参数
            invoice_id = request.GET.get('id', '').strip()
            
            if not invoice_id:
                return Response({
                    "code": 400,
                    "status": "error",
                    "message": "发票ID不能为空"
                }, status=400)
            
            print(f"查询发票详情，ID: {invoice_id}")
            
            # 1. 查询发票基本信息
            invoice_sql = """
                SELECT 
                    id,
                    invoice_type,
                    invoice_code,
                    issue_date,
                    buyer_name,
                    buyer_tax_id,
                    buyer_address_phone,
                    buyer_bank_account,
                    seller_name,
                    seller_tax_id,
                    seller_address_phone,
                    seller_bank_account,
                    check_code,
                    machine_code,
                    total_amount,
                    total_tax,
                    total_price_tax,
                    drawer,
                    created_at,
                    updated_at
                FROM invoice
                WHERE id = :invoice_id
            """
            
            invoice_params = {"invoice_id": invoice_id}
            invoice_info = MySqlDb.query_one(invoice_sql, invoice_params)
            
            if not invoice_info:
                return Response({
                    "code": 404,
                    "status": "error",
                    "message": "发票不存在"
                }, status=404)
            
            # 2. 查询发票明细项
            items_sql = """
                SELECT 
                    id,
                    item_name,
                    specification,
                    unit,
                    quantity,
                    unit_price,
                    amount,
                    tax_rate,
                    tax_amount,
                    created_at,
                    updated_at
                FROM invoice_item
                WHERE invoice_id = :invoice_id
                ORDER BY id ASC
            """
            
            items_params = {"invoice_id": invoice_id}
            items_list = MySqlDb.query(items_sql, items_params)
            
            # 3. 组装完整的JSON数据
            # 将null值转换为空字符串
            invoice_detail = {}
            for key, value in invoice_info.items():
                invoice_detail[key] = value if value is not None else ''
            
            # 添加items数组
            invoice_detail['items'] = []
            for item in items_list:
                item_dict = {}
                for key, value in item.items():
                    item_dict[key] = value if value is not None else ''
                invoice_detail['items'].append(item_dict)
            
            print(f"查询成功，发票详情: {invoice_detail}")
            
            # 返回成功响应
            return Response({
                "code": 200,
                "status": "success",
                "message": "查询成功",
                "data": invoice_detail
            }, status=200)
            
        except Exception as e:
            # 返回错误响应
            print(f"查询发票详情失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return Response({
                "code": 500,
                "status": "error",
                "message": f"查询失败: {str(e)}"
            }, status=500)



#根据发票ID查询发票图片接口
class QueryInvoiceImage(APIView):
    def get(self, request):
        """
        根据发票ID查询发票图片URL
        :param request: 请求对象，包含参数id（发票ID）
        :return: 发票图片完整访问URL
        """
        try:
            # 获取发票ID参数
            invoice_id = request.GET.get('id', '').strip()
            
            if not invoice_id:
                return Response({
                    "code": 400,
                    "status": "error",
                    "message": "发票ID不能为空"
                }, status=400)
            
            print(f"查询发票图片，ID: {invoice_id}")
            
            # 查询发票file_path字段
            sql = """
                SELECT 
                    id,
                    invoice_code,
                    file_path
                FROM invoice
                WHERE id = :invoice_id
            """
            
            params = {"invoice_id": invoice_id}
            invoice_info = MySqlDb.query_one(sql, params)
            
            if not invoice_info:
                return Response({
                    "code": 404,
                    "status": "error",
                    "message": "发票不存在"
                }, status=404)
            
            file_path = invoice_info.get('file_path', '')
            
            # 如果file_path为空，返回提示
            if not file_path:
                return Response({
                    "code": 404,
                    "status": "error",
                    "message": "该发票暂无图片"
                }, status=404)
            
            # 拼接完整的图片访问URL
            # 从请求中获取域名和端口
            host = request.get_host()  # 例如: localhost:8000
            scheme = 'https' if request.is_secure() else 'http'
            
            # 如果file_path已经是完整URL，直接返回
            if file_path.startswith('http://') or file_path.startswith('https://'):
                image_url = file_path
            else:
                # 拼接完整URL: http://localhost:8000/static/invoice_files/xxx.png
                image_url = f"{scheme}://{host}{file_path}"
            
            print(f"发票图片URL: {image_url}")
            
            # 返回成功响应
            return Response({
                "code": 200,
                "status": "success",
                "message": "查询成功",
                "data": {
                    "id": invoice_info.get('id'),
                    "invoice_code": invoice_info.get('invoice_code'),
                    "file_path": file_path,
                    "image_url": image_url
                }
            }, status=200)
            
        except Exception as e:
            # 返回错误响应
            print(f"查询发票图片失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return Response({
                "code": 500,
                "status": "error",
                "message": f"查询失败: {str(e)}"
            }, status=500)

#导出发票Excel
class ExportInvoiceExcel(APIView):
    """
    批量导出发票到Excel文件
    功能：根据选中的发票ID列表，查询发票数据并导出为Excel文件
    注意：使用表格式模板，忽略项目明细
    """
    def post(self, request):
        temp_file_path = None
        try:
            # 1. 获取请求参数：发票ID数组
            invoice_ids = request.data.get('invoice_ids', [])
            
            if not invoice_ids or not isinstance(invoice_ids, list):
                return Response({
                    "code": 400,
                    "status": "error",
                    "message": "请选择至少一条发票记录"
                }, status=400)
            
            print(f"导出发票，选中ID数量: {len(invoice_ids)}")
            print(f"发票IDs: {invoice_ids}")
            
            # 2. 根据ID数组查询发票数据
            # 构造IN查询条件
            placeholders = ','.join([f':id{i}' for i in range(len(invoice_ids))])
            params = {f'id{i}': invoice_id for i, invoice_id in enumerate(invoice_ids)}
            
            sql = f"""
                SELECT 
                    id,
                    invoice_type,
                    invoice_code,
                    issue_date,
                    buyer_name,
                    buyer_tax_id,
                    buyer_address_phone,
                    buyer_bank_account,
                    seller_name,
                    seller_tax_id,
                    seller_address_phone,
                    seller_bank_account,
                    total_amount,
                    total_tax,
                    total_price_tax,
                    drawer,
                    check_code,
                    machine_code
                FROM invoice
                WHERE id IN ({placeholders})
                ORDER BY created_at DESC
            """
            
            invoice_list = MySqlDb.query(sql, params)
            
            if not invoice_list:
                return Response({
                    "code": 404,
                    "status": "error",
                    "message": "未查询到发票数据"
                }, status=404)
            
            print(f"查询到 {len(invoice_list)} 条发票数据")
            
            # 3. 准备模板路径和临时输出路径
            project_path = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
            template_path = os.path.join(project_path, 'excel_templ', 'invoice_export_template(many).xlsx')
            temp_file_path = os.path.join(project_path, 'invoice_output.xlsx')
            
            # 检查模板文件是否存在
            if not os.path.exists(template_path):
                return Response({
                    "code": 404,
                    "status": "error",
                    "message": f"模板文件不存在: {template_path}"
                }, status=404)
            
            # 4. 转换数据并生成Excel（严格按照表格模板，忽略项目明细）
            self._convert_and_export_invoices(invoice_list, template_path, temp_file_path)
            
            # 5. 读取生成的Excel文件
            with open(temp_file_path, 'rb') as excel_file:
                excel_data = excel_file.read()
            
            # 6. 创建HTTP响应，设置正确的Content-Type
            response = HttpResponse(
                excel_data,
                content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
            )
            response['Content-Disposition'] = 'attachment; filename="invoice_output.xlsx"'
            
            print(f"成功导出 {len(invoice_list)} 条发票数据")
            
            return response
            
        except Exception as e:
            print(f"导出发票失败: {str(e)}")
            import traceback
            traceback.print_exc()
            return Response({
                "code": 500,
                "status": "error",
                "message": f"导出失败: {str(e)}"
            }, status=500)
        finally:
            # 7. 删除临时文件
            if temp_file_path and os.path.exists(temp_file_path):
                try:
                    os.remove(temp_file_path)
                    print(f"临时文件已删除: {temp_file_path}")
                except Exception as e:
                    print(f"删除临时文件失败: {str(e)}")
    
    def _convert_and_export_invoices(self, invoice_list, template_path, output_path):
        """
        将发票数据转换并导出到Excel模板（表格式）
        注意：忽略项目明细，只导出发票基本信息
        
        :param invoice_list: 发票数据列表（来自数据库查询结果）
        :param template_path: 模板文件路径
        :param output_path: 输出文件路径
        """
        # 加载模板
        wb = load_workbook(template_path)
        ws = wb.active
        
        # 表格列顺序（第1行是表头）：
        # A:受票类型, B:发票代码, C:发票号码, D:价税合计(大写), E:价税合计(小写)
        # F:开票日期, G:开票人, H:销售方名称, I:销售方纳税人识别号
        # J:销售方地址电话, K:销售方银行账号, L:购买方名称, M:购买方纳税人识别号
        # N:购买方银行账号, O:备注
        
        # 从第2行开始填充数据
        start_row = 2
        
        for idx, invoice_data in enumerate(invoice_list):
            row = start_row + idx
            
            # 填充表格数据（严格按照模板列顺序，忽略项目明细）
            ws[f'A{row}'] = invoice_data.get('invoice_type', '') or ''  # 发票类型
            ws[f'B{row}'] = invoice_data.get('invoice_code', '') or ''  # 发票代码（可能为空）
            ws[f'C{row}'] = invoice_data.get('invoice_code', '') or ''  # 受票号码
            # D列咎E列是价税合计，需要大写和小写，这里先填充小写
            ws[f'D{row}'] = ''  # 价税合计(大写) - 需要转换函数
            ws[f'E{row}'] = invoice_data.get('total_price_tax', '') or ''  # 价税合计(小写)
            ws[f'F{row}'] = str(invoice_data.get('issue_date', '')) or ''  # 开票日期
            ws[f'G{row}'] = invoice_data.get('drawer', '') or ''  # 开票人
            ws[f'H{row}'] = invoice_data.get('seller_name', '') or ''  # 销售方名称
            ws[f'I{row}'] = invoice_data.get('seller_tax_id', '') or ''  # 销售方纳税人识别号
            ws[f'J{row}'] = invoice_data.get('seller_address_phone', '') or ''  # 销售方地址电话
            ws[f'K{row}'] = invoice_data.get('seller_bank_account', '') or ''  # 销售方银行账号
            ws[f'L{row}'] = invoice_data.get('buyer_name', '') or ''  # 购买方名称
            ws[f'M{row}'] = invoice_data.get('buyer_tax_id', '') or ''  # 购买方纳税人识别号
            ws[f'N{row}'] = invoice_data.get('buyer_bank_account', '') or ''  # 购买方银行账号
            ws[f'O{row}'] = ''  # 备注
        
        # 保存文件
        wb.save(output_path)
        print(f"Excel文件已生成: {output_path}，共导出 {len(invoice_list)} 条发票数据")